package org.loda.util;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.loda.annotation.Primary;
import org.loda.annotation.Table;
import org.loda.enumeration.PessimisticLock;
import org.loda.exception.BeginTransactionException;
import org.loda.exception.ExecuteException;
import org.loda.exception.FieldNotFoundException;
import org.loda.exception.NoPrimaryKeyException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class Db {
	
	private static final Logger logger=LoggerFactory.getLogger(Db.class);
	
	public static int save(Object o) {
		return executeDMLSQL(createInsertSQL(o), generateInsertParams(o));
	}

	public static int update(Object o) {
		List<Object> params = generateUpdateParams(o);
		params.addAll(generateParamsByKey(o));
		return executeDMLSQL(createUpdateSQL(o), params);
	}

	public static int delete(Object o) {
		return executeDMLSQL(createDeleteSQL(o), generateParamsByKey(o));
	}

	private static List<Object> generateUpdateParams(Object o) {
		Field[] fields = o.getClass().getDeclaredFields();
		List<Object> list = new ArrayList<Object>();
		for (Field field : fields) {
			Primary key = field.getAnnotation(Primary.class);
			if (key == null) {
				// 不是主键
				list.add(getFieldValue(o, field));
			}
		}
		return list;
	}

	private static List<Object> generateInsertParams(Object o) {
		Field[] fields = o.getClass().getDeclaredFields();
		List<Object> list = new ArrayList<Object>();
		for (Field field : fields) {
			Primary key = field.getAnnotation(Primary.class);
			if (key == null || !key.autoIncreament()) {
				// 不是自增主键
				list.add(getFieldValue(o, field));
			}
		}
		return list;
	}

	private static List<Object> generateParamsByKey(Object o) {
		Field[] fields = o.getClass().getDeclaredFields();
		List<Object> list = new ArrayList<Object>();
		for (Field field : fields) {
			if (field.getAnnotation(Primary.class) != null) {
				list.add(getFieldValue(o, field));
			}
		}
		if (list.size() == 0)
			throw new NoPrimaryKeyException("没有主键");
		return list;
	}

	private static Object getFieldValue(Object o, Field field) {
		Method m = null;
		try {
			m = o.getClass().getMethod(getGetterName(field.getName()));
			return m.invoke(o);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static <T> List<T> findAll(Class<T> clazz) {
		String sql = createQueryAllSQL(clazz);
		return executeQueryAll(sql, clazz);
	}

	public static <T> T findById(Object id, Class<T> clazz) {
		return findById(id, clazz, PessimisticLock.CLOSE);
	}

	public static <T> T findById(Object id, Class<T> clazz, PessimisticLock lock) {
		String sql = createQueryOneSQL(id, clazz, lock);
		return executeQueryOne(sql, clazz);
	}

	private static int executeDMLSQL(String sql, List<Object> params) {
		StringBuilder sb = new StringBuilder(sql).append("\n参数为：");
		PreparedStatement ps = null;
		try {
			Connection conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < params.size(); i++) {
				ps.setObject(i + 1, params.get(i));
				sb.append(params.get(i) + "\t");
			}
			sb.append("\n");
			LogUtil.recordLog(sb.toString());
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw new ExecuteException("执行时异常", e);
		} finally {
			DBConnection.close(ps);
		}
	}

	private static String createDeleteSQL(Object o) {
		StringBuilder sb = new StringBuilder("delete from ");
		String condition = getConditionByPrimaryKey(o);
		return sb.append(getTableName(o.getClass())).append(condition)
				.toString();
	}

	private static <T> List<T> executeQueryAll(String sql, Class<T> clazz) {
		List<T> list = new ArrayList<T>();
		Connection conn = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			LogUtil.recordLog(sql);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				list.add(fillBlankModel(rs, clazz));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(ps, rs);
		}

		return list;
	}

	private static <T> T executeQueryOne(String sql, Class<T> clazz) {
		Connection conn = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			LogUtil.recordLog(sql);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				return fillBlankModel(rs, clazz);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(ps, rs);
		}
		return null;
	}

	/**
	 * 
	 * @Title: fillBlankModel
	 * @Description: 利用数据库取出来的数据填充model对象
	 * @param @param rs
	 * @param @param clazz
	 * @param @return
	 * @param @throws Exception 设定文件
	 * @return T 返回类型
	 * @throws
	 */
	private static <T> T fillBlankModel(ResultSet rs, Class<T> clazz)
			throws Exception {
		T t = clazz.newInstance();
		Field[] fields = clazz.getDeclaredFields();
		for (Field f : fields) {
			// 从数据库获取到属性值
			String fieldName = getFieldName(f);
			Method m = clazz.getMethod(getSetterName(f.getName()), f.getType());
			m.invoke(t, rs.getObject(fieldName));
		}
		return t;
	}

	private static <T> String createQueryOneSQL(Object id, Class<T> clazz,
			PessimisticLock lock) {
		StringBuilder sb = new StringBuilder("select * from ");
		String fieldName = null;
		try {
			fieldName = getFieldName(clazz.getDeclaredField("id"));
		} catch (Exception e) {
			throw new FieldNotFoundException("id属性没有找到", e);
		}
		sb.append(getTableName(clazz)).append(" where ").append(fieldName)
				.append("=" + id + " ");
		if (lock.status() == PessimisticLock.OPEN.status()) {
			sb.append(" for update ");
		}
		return sb.toString();
	}

	private static <T> String createQueryAllSQL(Class<T> clazz) {
		StringBuilder sb = new StringBuilder("select * from ");
		return sb.append(getTableName(clazz)).toString();
	}

	/**
	 * 
	 * @Title: getFieldName
	 * @Description: 获取字段名
	 * @param @param f
	 * @param @return 设定文件
	 * @return String 返回类型
	 * @throws
	 */
	private static String getFieldName(Field field) {
		org.loda.annotation.Field f = field
				.getAnnotation(org.loda.annotation.Field.class);
		if (f == null) {
			// 如果表级别注解为空，那么就直接引用类名
			return field.getName();
		}
		// 否则就利用table注解定义的表名
		return f.value();
	}

	/**
	 * '
	 * 
	 * @Title: getTableName
	 * @Description: 获取表名
	 * @param @param clazz
	 * @param @return 设定文件
	 * @return String 返回类型
	 * @throws
	 */
	private static <T> String getTableName(Class<T> clazz) {
		Table t = clazz.getAnnotation(Table.class);
		if (t == null) {
			// 如果表级别注解为空，那么就直接引用类名
			return clazz.getSimpleName();
		}
		// 否则就利用table注解定义的表名
		return t.value();
	}

	private static String createInsertSQL(Object o) {
		StringBuilder sb = new StringBuilder("insert into ");
		return sb.append(getTableName(o.getClass()))
				.append(getInsertContent(o)).toString();
	}

	private static String createUpdateSQL(Object o) {
		StringBuilder sb = new StringBuilder("update ");
		return sb.append(getTableName(o.getClass()))
				.append(getUpdateContent(o)).append(" ")
				.append(getConditionByPrimaryKey(o)).toString();
	}

	private static String getInsertContent(Object o) {
		Field[] fields = o.getClass().getDeclaredFields();
		StringBuilder sb = new StringBuilder("(");
		int i = 0;
		for (Field field : fields) {
			Primary key = field.getAnnotation(Primary.class);
			if (key == null || !key.autoIncreament()) {
				i++;
				// 不是自增主键
				sb.append(getFieldName(field)).append(",");
			}
		}
		sb.deleteCharAt(sb.length() - 1).append(") values(");
		for (int j = 0; j < i; j++) {
			sb.append("?,");
		}
		return sb.deleteCharAt(sb.length() - 1).append(")").toString();
	}

	private static String getUpdateContent(Object o) {
		StringBuilder sb = new StringBuilder(" set ");
		Field[] fields = o.getClass().getDeclaredFields();
		for (Field field : fields) {
			Primary key = field.getAnnotation(Primary.class);
			if (key == null) {
				// 不是主键
				sb.append(getFieldName(field) + "=?,");
			}
		}
		return sb.deleteCharAt(sb.length() - 1).toString();
	}

	private static String getConditionByPrimaryKey(Object o) {
		Field[] fields = o.getClass().getDeclaredFields();
		StringBuilder sb = new StringBuilder(" where ");
		boolean hasPrimaryKey = false;
		for (Field field : fields) {
			if (field.getAnnotation(Primary.class) != null) {
				hasPrimaryKey = true;
				try {
					sb.append(getFieldName(field) + "=? and");
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		if (!hasPrimaryKey)
			throw new NoPrimaryKeyException("没有主键");
		return sb.delete(sb.length() - 3, sb.length()).toString().trim();
	}

	/**
	 * 
	 * @Title: getSetterName
	 * @Description: 获取set方法名
	 * @param @param name
	 * @param @return 设定文件
	 * @return String 返回类型
	 * @throws
	 */
	private static String getSetterName(String name) {
		return generateMethodName(name, "set");
	}

	/**
	 * 
	 * @Title: getGetterName
	 * @Description:获取get方法名
	 * @param @param name
	 * @param @return 设定文件
	 * @return String 返回类型
	 * @throws
	 */
	private static String getGetterName(String name) {
		return generateMethodName(name, "get");
	}

	private static String generateMethodName(String name, String s) {
		String first = name.substring(0, 1).toUpperCase();
		return new StringBuilder(s).append(first).append(name.substring(1))
				.toString();
	}

	
	public static void begin(){
		try {
			DBConnection.getConnection().setAutoCommit(false);
			logger.info("开启事物");
		} catch (SQLException e) {
			throw new BeginTransactionException("开启事物失败",e);
		}
	}

	public static void rollback() {
		try {
			DBConnection.getConnection().rollback();
			logger.info("回滚事务");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBConnection.getConnection().setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				close();
			}
		}
	}

	public static void commit() {
		try {
			DBConnection.getConnection().commit();
			DBConnection.getConnection().setAutoCommit(true);
			logger.info("提交事务");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}

	private static void close() {
		logger.info("关闭数据库连接");
		DBConnection.close();
	}
}
